﻿using System.ServiceModel;
using System.ServiceModel.Activation;
using System.IO;
using System.Data.OleDb;
using System.Web.Configuration;
using System;
using System.Data.SqlClient;

namespace Perm.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class SaveReiting
    {
        SqlConnection conMSSQL;
        SqlDataReader readerMSSQL;
        SqlCommand commandMSSQL;

        DateTime thisDate = DateTime.Now.ToUniversalTime().AddHours(6);
        string String = "", StringMSSQL = "", end = "", error = null, stringDate="", city = "";
        int badRoadL;
        int goodRoadL;
        int badGasL;
        int goodGasL;
        int badCarwashL;
        int goodCarwashL;
        string[] badRoadS;
        string[] goodRoadS;
        string[] badGasS;
        string[] goodGasS;
        string[] badCarwashS;
        string[] goodCarwashS;

        [OperationContract]
        public string DoWork(string citylocal, string IP, string badRoad, string goodRoad, string badGas, string goodGas, string badCarwash, string goodCarwash)
        {
            city = citylocal;
            badRoadS = (badRoad != "") ? badRoad.Split(';') : null;
            goodRoadS = (goodRoad != "") ? goodRoad.Split(';') : null;
            badGasS = (badGas != "") ? badGas.Split(';') : null;
            goodGasS = (goodGas != "") ? goodGas.Split(';') : null;
            badCarwashS = (badCarwash != "") ? badCarwash.Split(';') : null;
            goodCarwashS = (goodCarwash != "") ? goodCarwash.Split(';') : null;
            badRoadL = (badRoadS != null) ? badRoadS.Length : 0;
            goodRoadL = (goodRoadS != null) ? goodRoadS.Length : 0;
            badGasL = (badGasS != null) ? badGasS.Length : 0;
            goodGasL = (goodGasS != null) ? goodGasS.Length : 0;
            badCarwashL = (badCarwashS != null) ? badCarwashS.Length : 0;
            goodCarwashL = (goodCarwashS != null) ? goodCarwashS.Length : 0;

            string connectionStringMSSQL = WebConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;

            try
            {
                conMSSQL = new SqlConnection(connectionStringMSSQL);
                conMSSQL.Open();
            }
            catch { error += " Невозможно подключиться к базе данных. MS SQL (Save raiting)"; }

            stringDate = thisDate.ToShortDateString();

            switch (city)
            {
                case "perm":
                    {
                        String = "SELECT IP.IP, IP.DATA FROM IP WHERE IP.IP='" + IP + "'";
                        break;
                    }
                case "ekb":
                    {
                        String = "SELECT IP_EKB.IP, IP_EKB.DATA FROM IP_EKB WHERE IP_EKB.IP='" + IP + "'";
                        break;
                    }
                case "permkray":
                    {
                        String = "SELECT IP_PERMKRAY.IP, IP_PERMKRAY.DATA FROM IP_PERMKRAY WHERE IP_PERMKRAY.IP='" + IP + "'";
                        break;
                    }
            }
            
            try {
                commandMSSQL = new SqlCommand(String, conMSSQL);
            } catch { error += " Невозможно получить IP адрес в базе. IP."; }
            
            if (commandMSSQL.ExecuteScalar() != null)
            {
                    if (save())
                    {
                        switch (city)
                        {
                            case "perm":
                                {
                                    String = "UPDATE IP SET IP.[IP] = '" + IP + "', IP.[DATA] = '" + thisDate + "' WHERE IP.[IP]='" + IP + "'";
                                    break;
                                }
                            case "ekb":
                                {
                                    String = "UPDATE IP_EKB SET IP_EKB.[IP] = '" + IP + "', IP_EKB.[DATA] = '" + thisDate + "' WHERE IP_EKB.[IP]='" + IP + "'";
                                    break;
                                }
                            case "permkray":
                                {
                                    String = "UPDATE IP_PERMKRAY SET IP_PERMKRAY.[IP] = '" + IP + "', IP_PERMKRAY.[DATA] = '" + thisDate + "' WHERE IP_PERMKRAY.[IP]='" + IP + "'";
                                    break;
                                }
                        }
                                                    
                            try {
                                commandMSSQL = new SqlCommand(String, conMSSQL);
                                commandMSSQL.ExecuteNonQuery();
                            } catch { error += " Невозможно обновить IP адрес в базе. IP."; }
                            end = "Ваша оценка сохранена. Спасибо.";                       
                    }
                    else
                    {
                        end = "Ошибка записи. Извините.";                    
                    }                
            }
            else
            {
                if (save())
                {
                    if (IP.Substring(0, 8) != "не")                        
                    {
                        switch (city)
                        {
                            case "perm":
                                {
                                    String = "INSERT INTO IP (DATA, IP) VALUES('" + thisDate + "', '" + IP + "')";
                                    break;
                                }
                            case "ekb":
                                {
                                    String = "INSERT INTO IP_EKB (DATA, IP) VALUES('" + thisDate + "', '" + IP + "')";
                                    break;
                                }
                            case "permkray":
                                {
                                    String = "INSERT INTO IP_PERMKRAY (DATA, IP) VALUES('" + thisDate + "', '" + IP + "')";
                                    break;
                                }
                        }
                        
                        try {
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        } catch { error += " Невозможно добавить IP адрес в базу. IP."; }  
                        end = "Ваша оценка сохранена. Спасибо.";
                    }
                    else
                    {
                        end = "Ваша оценка сохранена. Но и за-за ошибки на сервере ограничения на голосование сняты. Спасибо.";
                    }
                }
                else
                {
                    end = "Ошибка записи. Извините.";
                }
            }
            if (error != null)
            {
                switch (city)
                {
                    case "perm":
                        {
                            String = "INSERT INTO ERRORS (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                            break;
                        }
                    case "ekb":
                        {
                            String = "INSERT INTO ERRORS_EKB (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                            break;
                        }
                    case "permkray":
                        {
                            String = "INSERT INTO ERRORS_PERMKRAY (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                            break;
                        }
                }
                try
                {
                    commandMSSQL = new SqlCommand(String, conMSSQL);
                    commandMSSQL.ExecuteNonQuery();
                }
                catch { }
            }
            conMSSQL.Close();
            return end;
        }

        public bool save()
            {
                bool result=true;

                try
                {
                    //Сохраняем результаты
                    if (badRoadS != null)
                    {
                        for (int i = 0; i <= badRoadL - 1; i++)
                        {
                            switch (city)
                            {
                                case "perm":
                                    {
                                        String = "UPDATE RATING_ROAD_PERM SET RATING_ROAD_PERM.RATING = [RATING_ROAD_PERM].[RATING]-1, RATING_ROAD_PERM.VOTE = [RATING_ROAD_PERM].[VOTE]+1 WHERE RATING_ROAD_PERM.[ID]=" + badRoadS[i];
                                        break;
                                    }
                                case "ekb":
                                    {
                                        String = "UPDATE RATING_ROAD_EKB SET RATING_ROAD_EKB.RATING = [RATING_ROAD_EKB].[RATING]-1, RATING_ROAD_EKB.VOTE = [RATING_ROAD_EKB].[VOTE]+1 WHERE RATING_ROAD_EKB.[ID]=" + badRoadS[i];
                                        break;
                                    }
                                case "permkray":
                                    {
                                        String = "UPDATE RATING_ROAD_PERMKRAY SET RATING_ROAD_PERMKRAY.RATING = [RATING_ROAD_PERMKRAY].[RATING]-1, RATING_ROAD_PERMKRAY.VOTE = [RATING_ROAD_PERMKRAY].[VOTE]+1 WHERE RATING_ROAD_PERMKRAY.[ID]=" + badRoadS[i];
                                        break;
                                    }
                            }                            
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        }
                    }

                    if (goodRoadS != null)
                    {
                        for (int i = 0; i <= goodRoadL - 1; i++)
                        {
                            switch (city)
                            {
                                case "perm":
                                    {
                                        String = "UPDATE RATING_ROAD_PERM SET RATING_ROAD_PERM.RATING = [RATING_ROAD_PERM].[RATING]+1, RATING_ROAD_PERM.VOTE = [RATING_ROAD_PERM].[VOTE]+1 WHERE RATING_ROAD_PERM.[ID]=" + goodRoadS[i];
                                        break;
                                    }
                                case "ekb":
                                    {
                                        String = "UPDATE RATING_ROAD_EKB SET RATING_ROAD_EKB.RATING = [RATING_ROAD_EKB].[RATING]+1, RATING_ROAD_EKB.VOTE = [RATING_ROAD_EKB].[VOTE]+1 WHERE RATING_ROAD_EKB.[ID]=" + goodRoadS[i];
                                        break;
                                    }
                                case "permkray":
                                    {
                                        String = "UPDATE RATING_ROAD_PERMKRAY SET RATING_ROAD_PERMKRAY.RATING = [RATING_ROAD_PERMKRAY].[RATING]+1, RATING_ROAD_PERMKRAY.VOTE = [RATING_ROAD_PERMKRAY].[VOTE]+1 WHERE RATING_ROAD_PERMKRAY.[ID]=" + goodRoadS[i];
                                        break;
                                    }
                            } 
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        }
                    }

                    if (badGasS != null)
                    {
                        for (int i = 0; i <= badGasL - 1; i++)
                        {
                            switch (city)
                            {
                                case "perm":
                                    {
                                        String = "UPDATE RATING_GAS_PERM SET RATING_GAS_PERM.RATING = [RATING_GAS_PERM].[RATING]-1, RATING_GAS_PERM.VOTE = [RATING_GAS_PERM].[VOTE]+1 WHERE RATING_GAS_PERM.[ID]=" + badGasS[i];
                                        break;
                                    }
                                case "ekb":
                                    {
                                        String = "UPDATE RATING_GAS_EKB SET RATING_GAS_EKB.RATING = [RATING_GAS_EKB].[RATING]-1, RATING_GAS_EKB.VOTE = [RATING_GAS_EKB].[VOTE]+1 WHERE RATING_GAS_EKB.[ID]=" + badGasS[i];
                                        break;
                                    }
                                case "permkray":
                                    {
                                        String = "UPDATE RATING_GAS_PERMKRAY SET RATING_GAS_PERMKRAY.RATING = [RATING_GAS_PERMKRAY].[RATING]-1, RATING_GAS_PERMKRAY.VOTE = [RATING_GAS_PERMKRAY].[VOTE]+1 WHERE RATING_GAS_PERMKRAY.[ID]=" + badGasS[i];
                                        break;
                                    }
                            }                             
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        }
                    }

                    if (goodGasS != null)
                    {
                        for (int i = 0; i <= goodGasL - 1; i++)
                        {
                            switch (city)
                            {
                                case "perm":
                                    {
                                        String = "UPDATE RATING_GAS_PERM SET RATING_GAS_PERM.RATING = [RATING_GAS_PERM].[RATING]+1, RATING_GAS_PERM.VOTE = [RATING_GAS_PERM].[VOTE]+1 WHERE RATING_GAS_PERM.[ID]=" + goodGasS[i];
                                        break;
                                    }
                                case "ekb":
                                    {
                                        String = "UPDATE RATING_GAS_EKB SET RATING_GAS_EKB.RATING = [RATING_GAS_EKB].[RATING]+1, RATING_GAS_EKB.VOTE = [RATING_GAS_EKB].[VOTE]+1 WHERE RATING_GAS_EKB.[ID]=" + goodGasS[i];
                                        break;
                                    }
                                case "permkray":
                                    {
                                        String = "UPDATE RATING_GAS_PERMKRAY SET RATING_GAS_PERMKRAY.RATING = [RATING_GAS_PERMKRAY].[RATING]+1, RATING_GAS_PERMKRAY.VOTE = [RATING_GAS_PERMKRAY].[VOTE]+1 WHERE RATING_GAS_PERMKRAY.[ID]=" + goodGasS[i];
                                        break;
                                    }
                            }                            
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        }
                    }

                    if (badCarwashS != null)
                    {
                        for (int i = 0; i <= badCarwashL - 1; i++)
                        {
                            switch (city)
                            {
                                case "perm":
                                    {
                                        String = "UPDATE RATING_CARWASH_PERM SET RATING_CARWASH_PERM.RATING = [RATING_CARWASH_PERM].[RATING]-1, RATING_CARWASH_PERM.VOTE = [RATING_CARWASH_PERM].[VOTE]+1 WHERE RATING_CARWASH_PERM.[ID]=" + badCarwashS[i];
                                        break;
                                    }
                                case "ekb":
                                    {
                                        String = "UPDATE RATING_CARWASH_EKB SET RATING_CARWASH_EKB.RATING = [RATING_CARWASH_EKB].[RATING]-1, RATING_CARWASH_EKB.VOTE = [RATING_CARWASH_EKB].[VOTE]+1 WHERE RATING_CARWASH_EKB.[ID]=" + badCarwashS[i];
                                        break;
                                    }
                                case "permkray":
                                    {
                                        String = "UPDATE RATING_CARWASH_PERMKRAY SET RATING_CARWASH_PERMKRAY.RATING = [RATING_CARWASH_PERMKRAY].[RATING]-1, RATING_CARWASH_PERMKRAY.VOTE = [RATING_CARWASH_PERMKRAY].[VOTE]+1 WHERE RATING_CARWASH_PERMKRAY.[ID]=" + badCarwashS[i];
                                        break;
                                    }
                            }                            
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        }
                    }

                    if (goodCarwashS != null)
                    {
                        for (int i = 0; i <= goodCarwashL - 1; i++)
                        {
                            switch (city)
                            {
                                case "perm":
                                    {
                                        String = "UPDATE RATING_CARWASH_PERM SET RATING_CARWASH_PERM.RATING = [RATING_CARWASH_PERM].[RATING]+1, RATING_CARWASH_PERM.VOTE = [RATING_CARWASH_PERM].[VOTE]+1 WHERE RATING_CARWASH_PERM.[ID]=" + goodCarwashS[i];
                                        break;
                                    }
                                case "ekb":
                                    {
                                        String = "UPDATE RATING_CARWASH_EKB SET RATING_CARWASH_EKB.RATING = [RATING_CARWASH_EKB].[RATING]+1, RATING_CARWASH_EKB.VOTE = [RATING_CARWASH_EKB].[VOTE]+1 WHERE RATING_CARWASH_EKB.[ID]=" + goodCarwashS[i];
                                        break;
                                    }
                                case "permkray":
                                    {
                                        String = "UPDATE RATING_CARWASH_PERMKRAY SET RATING_CARWASH_PERMKRAY.RATING = [RATING_CARWASH_PERMKRAY].[RATING]+1, RATING_CARWASH_PERMKRAY.VOTE = [RATING_CARWASH_PERMKRAY].[VOTE]+1 WHERE RATING_CARWASH_PERMKRAY.[ID]=" + goodCarwashS[i];
                                        break;
                                    }
                            }                            
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        }
                    }
                    // Обновляем ежедневную статистику
                    switch (city)
                    {
                        case "perm":
                            {
                                String = "SELECT top 1 Max(STATS.[NO]) AS [Max-NO], STATS.DATA FROM STATS GROUP BY STATS.DATA ORDER BY Max(STATS.[NO]) DESC";                    
                                break;
                            }
                        case "ekb":
                            {
                                String = "SELECT top 1 Max(STATS_EKB.[NO]) AS [Max-NO], STATS_EKB.DATA FROM STATS_EKB GROUP BY STATS_EKB.DATA ORDER BY Max(STATS_EKB.[NO]) DESC";                    
                                break;
                            }
                        case "permkray":
                            {
                                String = "SELECT top 1 Max(STATS_PERMKRAY.[NO]) AS [Max-NO], STATS_PERMKRAY.DATA FROM STATS_PERMKRAY GROUP BY STATS_PERMKRAY.DATA ORDER BY Max(STATS_PERMKRAY.[NO]) DESC";                    
                                break;
                            }
                    }                    
                    try
                    {
                        commandMSSQL = new SqlCommand(String, conMSSQL);
                        readerMSSQL = commandMSSQL.ExecuteReader();
                        readerMSSQL.Read();
                    }
                    catch
                    { error += " Невозможно подключиться к базе. Статистика."; }
                    
                    DateTime oldDate = new DateTime();
                    string Date = "";
                    int NO = 0;
                    try {
                        Date = readerMSSQL[1].ToString();
                        NO = readerMSSQL.GetInt32(0);
                        oldDate = Convert.ToDateTime(Date);
                        Date = oldDate.ToShortDateString();
                    } catch { error += " Невозможно считать данные. Статистика."; }

                    if (stringDate==Date)
                    {
                        switch (city)
                        {
                            case "perm":
                                {
                                    String = "UPDATE STATS SET STATS.DATA = '" + thisDate + "', STATS.DAY_COUNT_VOTE = [STATS].[DAY_COUNT_VOTE] + 1, STATS.BAD_ROAD = [STATS].[BAD_ROAD] + " + badRoadL + ", STATS.GOOD_ROAD = [STATS].[GOOD_ROAD] + " + goodRoadL + ", STATS.BAD_GAS = [STATS].[BAD_GAS] + " + badGasL + ", STATS.GOOD_GAS = [STATS].[GOOD_GAS] + " + goodGasL + ", STATS.BAD_CARWASH = [STATS].[BAD_CARWASH] + " + badCarwashL + ", STATS.GOOD_CARWASH = [STATS].[GOOD_CARWASH] + " + goodCarwashL + "  WHERE STATS.NO = " + NO;
                                    break;
                                }
                            case "ekb":
                                {
                                    String = "UPDATE STATS_EKB SET STATS_EKB.DATA = '" + thisDate + "', STATS_EKB.DAY_COUNT_VOTE = [STATS_EKB].[DAY_COUNT_VOTE] + 1, STATS_EKB.BAD_ROAD = [STATS].[BAD_ROAD] + " + badRoadL + ", STATS_EKB.GOOD_ROAD = [STATS_EKB].[GOOD_ROAD] + " + goodRoadL + ", STATS_EKB.BAD_GAS = [STATS_EKB].[BAD_GAS] + " + badGasL + ", STATS_EKB.GOOD_GAS = [STATS_EKB].[GOOD_GAS] + " + goodGasL + ", STATS_EKB.BAD_CARWASH = [STATS_EKB].[BAD_CARWASH] + " + badCarwashL + ", STATS_EKB.GOOD_CARWASH = [STATS_EKB].[GOOD_CARWASH] + " + goodCarwashL + "  WHERE STATS_EKB.NO = " + NO;                                    
                                    break;
                                }
                            case "permkray":
                                {
                                    String = "UPDATE STATS_PERMKRAY SET STATS_PERMKRAY.DATA = '" + thisDate + "', STATS_PERMKRAY.DAY_COUNT_VOTE = [STATS_EKB].[DAY_COUNT_VOTE] + 1, STATS_EKB.BAD_ROAD = [STATS].[BAD_ROAD] + " + badRoadL + ", STATS_EKB.GOOD_ROAD = [STATS_EKB].[GOOD_ROAD] + " + goodRoadL + ", STATS_EKB.BAD_GAS = [STATS_EKB].[BAD_GAS] + " + badGasL + ", STATS_EKB.GOOD_GAS = [STATS_EKB].[GOOD_GAS] + " + goodGasL + ", STATS_EKB.BAD_CARWASH = [STATS_EKB].[BAD_CARWASH] + " + badCarwashL + ", STATS_EKB.GOOD_CARWASH = [STATS_EKB].[GOOD_CARWASH] + " + goodCarwashL + "  WHERE STATS_EKB.NO = " + NO;                                    
                                    break;
                                }
                        }                        
                        try {
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        } catch { error += " Невозможно обновить данные в базу. Статистика."; }
                    }
                    else
                    {
                        switch (city)
                        {
                            case "perm":
                                {
                                    String = "INSERT INTO STATS (DATA,DAY_COUNT_VOTE,BAD_ROAD,GOOD_ROAD,BAD_GAS,GOOD_GAS,BAD_CARWASH,GOOD_CARWASH) VALUES ('" + thisDate + "','1','" + badRoadL + "','" + goodRoadL + "','" + badGasL + "','" + goodGasL + "','" + badCarwashL + "','" + goodCarwashL + "')";
                                    break;
                                }
                            case "ekb":
                                {
                                    String = "INSERT INTO STATS_EKB (DATA,DAY_COUNT_VOTE,BAD_ROAD,GOOD_ROAD,BAD_GAS,GOOD_GAS,BAD_CARWASH,GOOD_CARWASH) VALUES ('" + thisDate + "','1','" + badRoadL + "','" + goodRoadL + "','" + badGasL + "','" + goodGasL + "','" + badCarwashL + "','" + goodCarwashL + "')";                                    
                                    break;
                                }
                            case "permkray":
                                {
                                    String = "INSERT INTO STATS_PERMKRAY (DATA,DAY_COUNT_VOTE,BAD_ROAD,GOOD_ROAD,BAD_GAS,GOOD_GAS,BAD_CARWASH,GOOD_CARWASH) VALUES ('" + thisDate + "','1','" + badRoadL + "','" + goodRoadL + "','" + badGasL + "','" + goodGasL + "','" + badCarwashL + "','" + goodCarwashL + "')";                                    
                                    break;
                                }
                        }                        
                        try {
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        } catch { error += " Невозможно добавить данные в базу данных. Статистика."; }
                    }
                } catch { result = false; }
                return result;
            }
        // Add more operations here and mark them with [OperationContract]
    }
}
